package com.myres.product.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.myres.db.ConnectionPool;
import com.myres.model.Product;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class ProductService {
	public List<Product> getProductsOfCategory(int cid) {
		Connection con=null;
		try{
		con=ConnectionPool.getPool().getConnection();
		PreparedStatement pstmt=con.prepareStatement("select * from res_products where _state <>1 and _sid in (select _id from res_series where _cid=?)  ");
		pstmt.setInt(1, cid);
		ResultSet rs=pstmt.executeQuery();
		List<Product> plist=Product.populate(rs);
		return plist;
	}catch(SQLException e){
		e.printStackTrace();
		return null;
	}finally{
		try{
		con.close();
		}catch(Exception e){
			
		}
	}
	}
	
	public List<Product> getProductsOfCategory(int cid,int shopId) {
		Connection con=null;
		try{
		con=ConnectionPool.getPool().getConnection();
		PreparedStatement pstmt=con.prepareStatement("select * from res_products where _state <>1 and _shop_id=? and _sid in (select _id from res_series where _cid=?)  ");
		pstmt.setInt(1, cid);
		pstmt.setInt(2, shopId);
		ResultSet rs=pstmt.executeQuery();
		List<Product> plist=Product.populate(rs);
		return plist;
	}catch(SQLException e){
		e.printStackTrace();
		return null;
	}finally{
		try{
		con.close();
		}catch(Exception e){
			
		}
	}
	}
	public List<Product> getProductsOfShop(int shopId) {
		Connection con=null;
		try{
		con=ConnectionPool.getPool().getConnection();
		PreparedStatement pstmt=con.prepareStatement("select * from res_products where _state <>1 and _shop_id =?");
		pstmt.setInt(1, shopId);
		ResultSet rs=pstmt.executeQuery();
		List<Product> plist=Product.populate(rs);
		return plist;
	}catch(SQLException e){
		e.printStackTrace();
		return null;
	}finally{
		try{
		con.close();
		}catch(Exception e){
			
		}
	}
	}
	public List<Product> getSmsProducts() {
		Connection con=null;
		try{
		con=ConnectionPool.getPool().getConnection();
		PreparedStatement pstmt=con.prepareStatement("select * from res_products where _sms_code is not null order by _sms_code asc");
		
		ResultSet rs=pstmt.executeQuery();
		List<Product> plist=Product.populate(rs);
		return plist;
	}catch(SQLException e){
		e.printStackTrace();
		return null;
	}finally{
		try{
		con.close();
		}catch(Exception e){
			
		}
	}
	}
	public Product getProductById(int id){
		Connection con=null;
		try{
		con=ConnectionPool.getPool().getConnection();
		PreparedStatement pstmt=con.prepareStatement("select * from res_products where _id=?");
		pstmt.setInt(1, id);
		ResultSet rs=pstmt.executeQuery();
		List<Product> plist=Product.populate(rs);
		if(plist==null||plist.size()==0) return null;
		return plist.get(0);
	}catch(SQLException e){
		e.printStackTrace();
		return null;
	}finally{
		try{
		con.close();
		}catch(Exception e){
			
		}
	}
	}
	public int updateProduct(Product p){
		StringBuffer sb=new StringBuffer("UPDATE res_products SET ");
		if(p.getName()!=null) sb.append(" _name='").append(p.getName()).append("',");
	    sb.append(" ").append(" _price=").append(p.getPrice()).append(",");
		sb.append("").append(" _on_index=").append(p.getOnIndex()).append(",");
		sb.append(" ").append(" _on_recommend=").append(p.getOnRecommend()).append(",");
		sb.append(" ").append(" _state=").append(p.getState());
		sb.append(" WHERE _id=").append(p.getId());
		String sql=sb.toString();
		System.out.print("sql="+sql);
		Connection con=null;
		try{
		con=ConnectionPool.getPool().getConnection();
		PreparedStatement pstmt=con.prepareStatement(sql);
		return pstmt.executeUpdate();
	   }catch(SQLException e){
		e.printStackTrace();
		return -1;
	  }finally{
		try{
		con.close();
		}catch(Exception e){
			
		}
	}
	}
	public int updateProduct(String table,Map<String,Object> fields,String where,int id){
		StringBuffer sql=new StringBuffer("UPDATE "+table +" ");
		for(Map.Entry<String, Object> entry:fields.entrySet()){
			sql.append(entry.getKey()+"=");
			if(entry.getValue() instanceof String) sql.append("'"+entry.getValue()+"'");
			else if(entry.getValue() instanceof Integer) sql.append(entry.getValue());
			sql.append(",");
		}
		sql.deleteCharAt(sql.length()-1);
		if(where!=null) sql.append(" where ").append("=").append(id);
		String sqlstring=sql.toString();
		System.out.print(sqlstring);
		Connection con=null;
		try{
		con=ConnectionPool.getPool().getConnection();
		PreparedStatement pstmt=con.prepareStatement(sqlstring);
		return pstmt.executeUpdate();
	   }catch(SQLException e){
		e.printStackTrace();
		return -1;
	}finally{
		try{
		con.close();
		}catch(Exception e){
			
		}
	}

		
	}

}
